package com.gt.util.db;

import com.alibaba.fastjson.JSONObject;
import com.gt.util.cache.redis.jedis.JedisUtil;
import com.gt.util.model.Account;
import com.gt.util.model.Device;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @author WUREN
 * @desc 数据库操作Demo
 * @create 2019-01-30 16:47
 **/
public class DBDemo extends Thread {
    /**
     * 功能：使用原始SQL查询单条记录
     * 返回每条记录封装到Map
     */
    public static void demo1_1() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select * from account where nickName = ? and userName = ?";
            Object[] params = new Object[]{"wuren","56211xxxx@qq.com"};
            Map<String,Object> map = db.queryOne(sql, params);

            System.out.print("【queryOne-原始SQL】");
            System.out.println(JSONObject.toJSONString(map));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL查询单条记录
     * 返回记录映射到Bean对象
     */
    public static void demo1_2() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select * from account where nickName = ? and userName = ?";
            Object[] params = new Object[]{"wuren","56211xxxx@qq.com"};
            Account account = db.queryOne(sql, params, Account.class);

            System.out.print("【queryOne-原始SQL】");
            System.out.println(JSONObject.toJSONString(account));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板查询单条记录
     * 返回每条记录封装到Map
     */
    public static void demo1_3() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("nickName", "wuren");
            params.put("userName", "56211xxxx@qq.com");
            Map<String,Object> map = db.queryOne("account", "demo1_2", params);

            System.out.print("【queryOne-SQL模板】");
            System.out.println(JSONObject.toJSONString(map));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板查询单条记录
     * 返回记录映射到Bean对象
     */
    public static void demo1_4() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("nickName", "wuren");
            params.put("userName", "56211xxxx@qq.com");
            Account account = db.queryOne("account", "demo1_2", params, Account.class);

            System.out.print("【queryOne-SQL模板】");
            System.out.println(JSONObject.toJSONString(account));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL查询多条记录
     * 返回每条记录封装到Map，再将Map集合存入List
     */
    public static void demo2_1() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select * from account where status = ? order by nickName";
            Object[] params = new Object[]{1};
            List<Map<String,Object>> list = db.query(sql, params);

            System.out.print("【query-原始SQL】");
            System.out.println(JSONObject.toJSONString(list));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL查询多条记录
     * 返回记录映射到Bean对象，再将Bean存入List集合中
     */
    public static void demo2_2() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select * from account where status = ? order by nickName";
            Object[] params = new Object[]{1};
            List<Account> list = db.query(sql, params, Account.class);

            System.out.print("【query-原始SQL】");
            System.out.println(JSONObject.toJSONString(list));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板查询多条记录
     * 返回每条记录封装到Map，再将Map集合存入List
     */
    public static void demo2_3() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("status", 1);
            List<Map<String,Object>> list = db.query("account", "demo2_2", params);

            System.out.print("【query-SQL模板】");
            System.out.println(JSONObject.toJSONString(list));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板查询多条记录
     * 返回记录映射到Bean对象，再将Bean存入List集合中
     */
    public static void demo2_4() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("status", 1);
            List<Account> list = db.query("account", "demo2_2", params, Account.class);

            System.out.print("【query-SQL模板】");
            System.out.println(JSONObject.toJSONString(list));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL单值查询，返回 Long
     *
     */
    public static void demo3() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select count(1) from account";
            Long result = db.querySingleLong(sql, null);

            System.out.print("【querySingleLong】");
            System.out.println(JSONObject.toJSONString(result));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }


    /**
     * 功能：使用原始SQL单值查询，返回 Double
     */
    public static void demo4() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select count(1)+1.5 from account";
            Double result = db.querySingleDouble(sql, null);

            System.out.print("【querySingleDouble】");
            System.out.println(JSONObject.toJSONString(result));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL单值查询，返回 Float
     */
    public static void demo5() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select count(1)+1.5 from account";
            Float result = db.querySingleFloat(sql, null);

            System.out.print("【querySingleFloat】");
            System.out.println(JSONObject.toJSONString(result));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL单值查询，返回 String
     */
    public static void demo6() {
        DBUtil db = new DBUtil();
        try {
            String sql = "select nickName from account";
            String result = db.querySingleString(sql, null);

            System.out.print("【querySingleString】");
            System.out.println(JSONObject.toJSONString(result));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL插入一条记录
     */
    public static void demo7_1() {
        DBUtil db = new DBUtil();
        try {
            String sql = "insert into account(nickName,userName,password,salt,avatar) values(?,?,?,?,?)";
            Object[] params = new Object[]{"wuren_test", "wuren_test@qq.com", "1111", "2222", "0/test.jpg"};
            int rows = db.insert(sql, params);
            System.out.print("【insert-原始SQL】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板插入一条记录
     */
    public static void demo7_2() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("nickName", "beetl");
            params.put("userName", "wuren_test@qq.com");
            params.put("password", "1111");
            params.put("salt", "2222");
            params.put("avatar", "0/test.jpg");

            int rows = db.insert("account", "demo7_2", params);
            System.out.print("【insert-SQL模板】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL更新记录
     */
    public static void demo8_1() {
        DBUtil db = new DBUtil();
        try {
            String sql = "update account set status=1 where nickName=?";
            Object[] params = new Object[]{"beetl"};
            int rows = db.update(sql, params);

            System.out.print("【update-原始SQL】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板更新记录
     */
    public static void demo8_2() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("nickName", "beetl");
            int rows = db.update("account", "demo8_2", params);
            System.out.print("【update-SQL模板】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用原始SQL删除记录
     */
    public static void demo9_1() {
        DBUtil db = new DBUtil();
        try {
            String sql = "delete from account where nickname = ?";
            Object[] params = new Object[]{"wuren_test"};
            int rows = db.delete(sql, params);

            System.out.print("【delete-原始SQL】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板删除记录
     */
    public static void demo9_2() {
        DBUtil db = new DBUtil();
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("nickName", "beetl");
            int rows = db.delete("account", "demo9_2", params);
            System.out.print("【delete-SQL模板】");
            System.out.println(JSONObject.toJSONString(rows));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 功能：使用SQL模板，条件控制语句
     */
    public static void demo10() {
        DBUtil db = new DBUtil();
        try {
            Account acc = new Account();
            acc.setNickName("test");
            Map<String, Object> params = new HashMap<String, Object>();

            params.put("account", acc);
            List<Account> list = db.query("account", "demo10", params, Account.class);
            System.out.print("【SQL模板条件控制语句】");
            System.out.println(JSONObject.toJSONString(list));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    /**
     * 数据库事务
     */
    public static void demo11() {
        DBUtil db = new DBUtil();
        try {
            // 开启事务
            db.beginTransaction();
            String sql = "insert into account(nickName,userName,password,salt,avatar) values(?,?,?,?,?)";
            Object[] params = new Object[]{"should_rollback", "wuren_test@qq.com", "1111", "2222", "0/test.jpg"};
            int insertRows = db.insert(sql, params);

            // 异常SQL
            sql = "update account set field_not_exist = 0";
            int updateRows = db.update(sql, null);
            System.out.println("insertRows = "+insertRows+"  updateRows = "+updateRows );
            db.commitTransaction();
        }catch(Exception e) {
            try {
                System.out.println("demo12 执行异常 回滚");
                db.rollbackTransaction();
            } catch (SQLException exp) {
                exp.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            db.closeConnection();
        }
    }

    public static void main(String[] a) {
        // 查询单条记录
        demo1_1();
        demo1_2();
        demo1_3();
        demo1_4();

        // 查询多条记录
        demo2_1();
        demo2_2();
        demo2_3();
        demo2_4();

        // 查询单值 Long
        demo3();
        // 查询单值 Double
        demo4();
        // 查询单值 Float
        demo5();
        // 查询单值 String
        demo6();

        // 插入记录
        demo7_1();
        demo7_2();

        // 更新记录
        demo8_1();
        demo8_2();

        // 删除记录
        demo9_1();
        demo9_2();

        // SQL模板条件控制语句
        demo10();

        // 数据库事务
        demo11();
    }


    @Override
    public void run() {
    }
}
